global restrdir "U:\data"
global public "R:\Public"
global sswdata "R:\Public\XWave\SocialSecurityWealth2010\built\stata"
global rand "R:\Public\Contributions\Rand\RandHRS2016V2\stata"
global output "U:\output"

*-------------------------------------------------------------------------------
*This program computes age-specific estimates of Social Security wealth 
*for w2010 respondents with non-frozen DB plans in the private sector. 
*-------------------------------------------------------------------------------

*-------------------------------------------------------------------------------
*Create tempfiles to store public data vars
*-------------------------------------------------------------------------------

use $public/XWave/PenWealthImp2010/built/stata/PWI10A_R, clear
gen double temp1 = real(HHID)
gen double temp2 = real(PN)
gen double respondent = temp1*1000 + temp2

keep respondent DBFlag_10 HHID PN
tempfile pwi10a_r
save `pwi10a_r'

*-------------------------------------------------------------------------------
* 2010 DB wealth and earnings projection from PEP 
* worker-plan-projected year pseudo panel
*-------------------------------------------------------------------------------

*Pension wealth and earnings projections from PEP (private sector workers)
import delimited $restrdir\penval_2010_earnadj_22.csv, clear 
rename maxnrervd dbwealth

*Get PEP coders' flag for frozen/CB converted plans based off SPD's/F5500's  
tempfile codeids
preserve
	keep codeid
	duplicates drop 
	rename codeid CodingID
	merge 1:m CodingID using $restrdir/PlanInfo2010ReleaseV0, ///
	keepus(FrozenPlan)	
	keep if _merge==3
	duplicates drop	
	rename CodingID codeid
	save `codeids', replace
restore	

merge m:1 codeid using `codeids', keepus(FrozenPlan)
drop _merge

*Merge with unrestricted pension data 
merge m:1 respondent using `pwi10a_r' 
tab DBFlag_10 _merge 
//unmerged obs are either pure DC or public sector employees (i.e. excluded from pension calculator input)
//1 record does not have a codeid
keep if _merge==3
drop _merge

*Remove respondent-years in plans coded as (Frozen/CB converted) by HRS
drop if FrozenPlan==1

*Drop combo plans
drop if codeid>5000

*Keep unique respodents
keep HHID PN
duplicates drop 

*-------------------------------------------------------------------------------
* Match to SS wealth projections
*-------------------------------------------------------------------------------
merge 1:1 HHID PN using $sswdata/SSWEALTHP2010
assert _merge!=1
keep if _merge==3
drop _merge

*No prospective wealth estimates for claimants
tab R10CLAIMED
drop if R10CLAIMED==1 //drop claimants

*Get age in 2010 and survey weight
rename HHID hhid
rename PN pn
merge m:1 hhid pn using $public/Tracker/stata/trk2018tr_r, keepusing(mage mwgtr gender)
assert _merge!=1
drop if _merge!=3
drop _merge
rename mage age_2010
rename mwgtr MWGTR
rename hhid HHID
rename pn PN

*Keep if age b/w 50 and 70 (all non-claimants are < 70)
keep if inrange(age_2010,50,70)

*Merge with annuity factors
rename age_2010 age
rename gender sex

merge m:1 age sex using $restrdir/annfactors
assert _merge!=1
keep if _merge==3
drop _merge

*Compute benefit payments = (wealth/annuity factor for age and sex)

local rage "ER NR XA"

foreach s of local rage{
	
	gen R10SSBR`s' = R10SSWR`s'/annfactor	
	gen H10SSBR`s' = H10SSWR`s'/annfactor

} 


*Replace HH SSB by . if 0 since R or S have passed that age/no spouse
replace H10SSBRER=. if H10SSBRER==0 & S10SSWRER==. & R10SSWRER!=0
replace H10SSBRNR=. if H10SSBRNR==0 & S10SSWRNR==. & R10SSWRNR!=0
replace H10SSBRXA=. if H10SSBRXA==0 & S10SSWRXA==. & R10SSWRXA!=0

*Average SS benefit by age
matrix ssw = J(33,5,.)
forvalues a = 48/80{
	local j = `a'-47
	matrix ssw[`j',1] = `a'
	
	if `a'==62{
		qui sum R10SSBRER [aw=MWGTR]
		matrix ssw[`j',2]=r(mean)
		matrix ssw[`j',3]=r(N)		
		qui sum H10SSBRER [aw=MWGTR]
		matrix ssw[`j',4]=r(mean)
		matrix ssw[`j',5]=r(N)
	}
	else if `a'==65{
		qui sum R10SSBRNR [aw=MWGTR]
		matrix ssw[`j',2]=r(mean)
		matrix ssw[`j',3]=r(N)		
		qui sum H10SSBRNR [aw=MWGTR]
		matrix ssw[`j',4]=r(mean)
		matrix ssw[`j',5]=r(N)		
	}	
	else if `a'==70{
		qui sum R10SSBRXA [aw=MWGTR]
		matrix ssw[`j',2]=r(mean)
		matrix ssw[`j',3]=r(N)		
		qui sum H10SSBRXA [aw=MWGTR]
		matrix ssw[`j',4]=r(mean)
		matrix ssw[`j',5]=r(N)		
	}		
	
}

clear
svmat ssw
rename ssw1 age
rename ssw2 ssb_r //respondent
rename ssw3 N_r
rename ssw4 ssb_h //household
rename ssw5 N_h

local ages 62 65
foreach a of local ages{
	qui sum ssb_r if age==`a'
	gen ssbr`a' = r(mean)
	qui sum ssb_h if age==`a'
	gen ssbh`a' = r(mean)
	
}

replace N_r = 0 if age<62
replace N_h = 0 if age<62
replace N_r = N_r[_n-1] if inrange(age,63,64)
replace N_h = N_h[_n-1] if inrange(age,63,64)	
replace N_r = N_r[_n-1] if inrange(age,66,69)
replace N_h = N_h[_n-1] if inrange(age,66,69)
replace N_r = N_r[_n-1] if inrange(age,71,.)
replace N_h = N_h[_n-1] if inrange(age,71,.)	

gen diff1 = (ssbr65-ssbr62)/3
replace ssb_r = ssb_r[_n-1]+diff1 if ssb_r == . & age<65 & age>62
drop diff1
gen diff1 = (ssbh65-ssbh62)/3
replace ssb_h = ssb_h[_n-1]+diff1 if ssb_h == . & age<65 & age>62
drop diff1

sum ssb_r if age==70
gen ssbr70 = r(mean)
sum ssb_h if age==70
gen ssbh70 = r(mean)

gen diff2 = (ssbr70-ssbr65)/5
replace ssb_r = ssb_r[_n-1]+diff2 if ssb_r==. & age<70 & age>65
drop diff2
gen diff2 = (ssbh70-ssbh65)/5
replace ssb_h = ssb_h[_n-1]+diff2 if ssb_h==. & age<70 & age>65
drop diff2

replace ssb_r = 0 if age<62
replace ssb_h = 0 if age<62
replace ssb_r = ssbr70 if age>70
replace ssb_h = ssbh70 if age>70

keep age ssb_r N_r ssb_h N_h 
order age ssb_r N_r ssb_h N_h

export delimited $output/ssw10.csv, replace
